package com.xj.demoExcel.poi;

import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.Objects;

public class Excel_POI {

    public static void main(String[] args) {
        String filePath = "src/main/resources/学生信息表.xlsx";
        File file = new File(filePath);
        String value1 = "";
        try {
            if (isExcelFile(new FileInputStream(file))) {
                value1 = readExcel(filePath, new FileInputStream(file));
                System.out.println(value1);
            } else {
                throw new RuntimeException("该文件不是正常的excel文件。可能后缀被修改过");
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        String filePath2 = "src/main/resources/地区信息表.xls";
        File file2 = new File(filePath2);
        String value2 = "";
        try {
            if (isExcelFile(new FileInputStream(file2))) {
                value2 = readExcel(filePath2, new FileInputStream(file2));
                System.out.println(value2);
            } else {
                throw new RuntimeException("该文件不是正常的excel文件。可能后缀被修改过");
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        String outFile1 = "/Users/admin/Downloads/学生信息表_ByPOICreate.xlsx";
        createExcel(outFile1, value1);
        String outFile2 = "/Users/admin/Downloads/地区信息表_ByPOICreate.xls";
        createExcel(outFile2, value2);
    }

    /**
     * 判断文件是否为excel格式
     * 能够解决本质是excel表，但是后缀被修改为其他后缀的文件
     * 并且能够解决本质不是excel表，但是后缀被修改为xlsx后缀的文件。
     *
     * @param inputStream
     * @return
     */
    public static Boolean isExcelFile(InputStream inputStream) {
        boolean result = false;
        try {
            inputStream = FileMagic.prepareToCheckMagic(inputStream);
            FileMagic fileMagic = FileMagic.valueOf(inputStream);
            if (Objects.equals(fileMagic, FileMagic.OLE2) || Objects.equals(fileMagic, FileMagic.OOXML)) {
                result = true;
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }


    /**
     * 使用POI读取excel中的数据
     * <p>
     * 注意：
     * 如果表格中偶一列为空，那么使用加强for循环会跳过该列！！！
     * getPhysicalNumberOfRows会跳过空行，不统计个数
     * getPhysicalNumberOfCells会跳过空列，不统计个数
     * <p>
     * 表格中某一个单元格为空，那么加强遍历时会跳过该格子
     * 遍历的下标从0开始
     *
     * @param filePath
     * @param inputStream
     * @return
     */
    public static String readExcel(String filePath, InputStream inputStream) {
        JsonObject result = new JsonObject();
        Workbook workbook;
        try {
            if (filePath.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (filePath.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            } else {
                throw new RuntimeException("excel表格的格式不正确：" + filePath);
            }

            System.out.println("该文件一共有sheet数量：" + workbook.getNumberOfSheets());
            System.out.println();
            for (Sheet sheet : workbook) {

                JsonArray jsonArray = new JsonArray();
                System.out.println("当前sheet的名字为：" + sheet.getSheetName() + " 拥有的实际的行数为(不包含当前sheet的空行)：" + sheet.getPhysicalNumberOfRows());
                System.out.println("当前sheet的第一行的下标为：" + sheet.getFirstRowNum() + " 当前sheet的最后一行的下标为：" + sheet.getLastRowNum());
                System.out.println();
                for (Row row : sheet) {

                    System.out.println("当前行的编号为：" + row.getRowNum() + " 拥有的实际的列数为(不包含当前行的空列)：" + row.getPhysicalNumberOfCells());
                    JsonArray tmpArr = new JsonArray();

                    System.out.println("第一个格子的下标：" + row.getFirstCellNum());
                    System.out.println("最后一个格子的后一个格子下标：" + row.getLastCellNum());

                    for (int i = 0; i < row.getLastCellNum(); i++) {
                        Cell cell = row.getCell(i);

                        //处理遇到的空cell
                        if (cell == null) {
                            tmpArr.add(cell);
                            continue;
                        }

                        if (cell.getCellTypeEnum().equals(CellType.NUMERIC)) {
                            tmpArr.add(cell.getNumericCellValue());
                        } else {
                            tmpArr.add(cell.getStringCellValue());
                        }
                    }
                    jsonArray.add(tmpArr);
                }
                System.out.println();

                //将第一个sheet的数据存起来
                result.put(sheet.getSheetName(), jsonArray);
            }
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return result.encode();
    }

    /**
     * 创建excel文件。数据格式如下：key代表某个sheet的名字，value代表对应sheet中的内容
     * {
     * "Sheet1": [
     * ["省", "市", "县区"],
     * ["四川省", "泸州市", "泸县"],
     * ["云南省", "昆明市", "下马新村"]
     * ]
     * }
     * <p>
     * 注意：
     * 先创建excel对象，再将对象写入一个新创建的文件中
     */
    public static void createExcel(String filePath, String data) {
        Workbook workbook;
        if (filePath.endsWith(".xls")) {
            workbook = new HSSFWorkbook();
        } else if (filePath.endsWith(".xlsx")) {
            workbook = new XSSFWorkbook();
        } else {
            throw new RuntimeException("excel表格的格式不正确：" + filePath);
        }


        JsonObject jsonObject = new JsonObject(data);
        jsonObject.forEach(map -> {
            String sheetName = map.getKey();
            JsonArray jsonArray = (JsonArray) map.getValue();
            int size = jsonArray.size();
            Sheet sheet1 = workbook.createSheet(sheetName);

            //遍历二维数组，将数据填入excel
            for (int i = 0; i < size; i++) {
                //创建sheet,row,cell
                Row row = sheet1.createRow(i);

                JsonArray tmpArr = jsonArray.getJsonArray(i);
                int tmpSize = tmpArr.size();
                for (int j = 0; j < tmpSize; j++) {
                    Cell cell = row.createCell(j);

                    Object obj = tmpArr.getValue(j);
                    if (obj != null) {
                        cell.setCellValue(String.valueOf(obj));
                    }
                }
            }
        });

        //将excel对象写入文件
        File file = new File(filePath);
        try {
            if (!file.exists()) {
                file.createNewFile();
            } else {
                file.delete();
                file.createNewFile();
            }
            workbook.write(new FileOutputStream(file));
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        }
    }

}
